package DBLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Employee;

public class DBEmployee implements IFDBEmployee {
	private Connection con = DBConnection.getInstance().getDBcon();
	private static DBEmployee dbEmp;

	private DBEmployee() {}
	
	public static DBEmployee getInstance(){
		if(dbEmp==null) dbEmp = new DBEmployee();
		return dbEmp;
	}
	
	@Override
	public ArrayList<Employee> getAllEmployees(){
		return miscWhere("");
	}
	
	@Override
	public Employee getEmployee_byEmployeeID(int pid){
		return singleWhere("e.pid="+pid);
	}
	
	@Override
	public ArrayList<Employee> getAll_where(String wClause) {
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<Employee> searchRandom(String searchF) {
		
		return miscWhere("e.pid LIKE '"+searchF+"' OR fname LIKE '"+searchF+"%' OR lname LIKE '"+searchF+"%' OR address LIKE '"+searchF+"%' OR city LIKE '"+searchF+"%' OR postalCode LIKE '"+searchF+"%' OR country LIKE '"+searchF+"%' OR phone LIKE '"+searchF+"%' OR email LIKE '"+searchF+"%' OR username LIKE '"+searchF+"%'");
	}
	
	@Override
	public Employee getEmployee_byName(String name){
		return singleWhere("fname='"+name+"' OR lname='"+name+"'");
	}
	
	@Override
	public ArrayList<Employee> getAllEmployees_byPosition(String position){
		return miscWhere("position='"+position+"'");
	}
	
	
	
	@Override
	public int login(String username, String password){
		int pid = -1;
		
		String query = "SELECT pid FROM Employee WHERE username='"+username+"' AND password='"+password+"'";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			ResultSet res = stmt.executeQuery(query);
			
			if(res.next()) pid = res.getInt(1);
			
			stmt.close();
		}
		catch(Exception e){
			System.out.println("Invalid Username OR Password");
		}
		
		return pid;
	}
		
		
	@Override
	public int insert(Employee employee)
	{
		int rc = -1;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			// START Location 
			int locationId =  DBLocation.getInstance().insert(employee.getPostalCode(), employee.getCity(), employee.getCountry());
			// END Location
			
			// START People
			String query = "INSERT INTO People (fname,lname,phone,address,email,locationId) VALUES ('"+employee.getFname()+"','"+employee.getLname()+"','"+employee.getPhone()+"','"+employee.getAddress()+"','"+employee.getEmail()+"', "+locationId+")";
			
			rc = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			ResultSet res = stmt.getGeneratedKeys();
			
			if (res.next())
			{
				rc = res.getInt(1);
				
				// START Employee
				String query2 = "INSERT INTO Employee (pid,username,password,salary,position) VALUES ("+rc+",'"+employee.getUsername()+"','"+employee.getPassword()+"',"+employee.getSalary()+",'"+employee.getPosition()+"')";
				rc = stmt.executeUpdate(query2);
				//END Employee
			}
			// END People
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert Employee: " +e.getMessage());
		}
		return rc;
	}
	
	
	
	@Override
	public int update(Employee employee)
	{
		int rc = -1;
		
		
		try{
			Statement stmt = con.createStatement();
			
			stmt.setQueryTimeout(5);
			
			// START Location 
			int locationId =  DBLocation.getInstance().insert(employee.getPostalCode(), employee.getCity(), employee.getCountry());
			// END Location
			
			// START People
			String query = "UPDATE People SET fname = '"+employee.getFname()+"',lname ='"+employee.getLname()+"',phone ='"+employee.getPhone()+"',address ='"+employee.getAddress()+"',email ='"+employee.getEmail()+"',locationId="+locationId+" WHERE pid = "+employee.getPid();
			
			PreparedStatement psstmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			rc = psstmt.executeUpdate();
			ResultSet res = psstmt.getGeneratedKeys();
			
			if (res.next())
			{
				rc = res.getInt(1);
				
				// START Employee
				String query2 = "UPDATE Employee set username='"+employee.getUsername()+"',password='"+employee.getPassword()+"',salary="+employee.getSalary()+",position='"+employee.getPosition()+"' WHERE pid="+employee.getPid();
				rc = stmt.executeUpdate(query2);
				//END Employee
			}
			// END People
			stmt.close();
		}	
		catch(Exception e){
			System.err.println("Could not update the Employee "+e.getMessage());
			e.printStackTrace();
		}
				
		return rc;
	}
	
	
	
	
	
	@Override
	public int delete(int pid)
	{
		int rc =-1;
		String query = "DELETE FROM People WHERE pid="+pid;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not delete Employee "+e.getMessage());
		}
		
		return rc;
	}
	
	private Employee singleWhere(String wClause)
	{
		Employee employee = new Employee();
		
		ResultSet result;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				employee = buildEmployee(result);
			}
			stmt.close();
			}
		catch(Exception e){
			System.err.println("Could not execute select Employee query "+e.getMessage());
		}
		
		return employee;
	}
	
	
	private ArrayList<Employee> miscWhere(String wClause)
	{
		ArrayList<Employee> employees = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				employees.add(buildEmployee(results));
			}
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not execute multiple select Employee query "+e.getMessage());
		}
		return employees;
	}
	
	private String buildQuery(String wClause){
		String query = "SELECT e.pid,username,password,salary,position,fname,lname,phone,address,email,regDate,city,postalCode,country FROM Employee e INNER JOIN People p ON e.pid=p.pid INNER JOIN Location loc ON p.locationId=loc.locationId";
		
		if(wClause.length()>0) query += " WHERE "+wClause;
		
		return query;
	}
	
	private Employee buildEmployee(ResultSet result){
		Employee employee = new Employee();
		try{
			employee.setPid(result.getInt("pid"));
			employee.setUsername(result.getString("username"));
			employee.setPassword(result.getString("password"));
			employee.setSalary(result.getDouble("salary"));
			employee.setPosition(result.getString("position"));
			employee.setFname(result.getString("fname"));
			employee.setLname(result.getString("lname"));
			employee.setPhone(result.getString("phone"));
			employee.setAddress(result.getString("address"));
			employee.setEmail(result.getString("email"));
			employee.setDate(result.getTimestamp("regDate"));
			employee.setPostalCode(result.getString("postalCode"));
			employee.setCity(result.getString("city"));
			employee.setCountry(result.getString("country"));
			
			
		} catch (Exception e) {
			System.err.println("Could not build the Employee result "+e.getMessage());
		}
		return employee;
	}
}
